﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using ThanhTu.GNCMedia.KQXS.DataAccess.DO;
using ThanhTu.GNCMedia.KQXS.Common;

namespace ThanhTu.GNCMedia.KQXS.DataAccess.DAL
{
    public class KindOfAwardsDAL : BaseDAL
    {
        public KindOfAwardsDAL() : base() { }

        public int add(KindOfAwardsDO vKindOfAwardsDO)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "KindAward_PKG.SaveKindAward";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("INID_AWARD", OracleDbType.Int32);
            oraParam.Value = vKindOfAwardsDO.AwardsID;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("INAWARD_NAME", OracleDbType.Varchar2);
            oraParam.Value = vKindOfAwardsDO.AwardsName;
            oraComm.Parameters.Add(oraParam);


            oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            int result = ExecuteNoneQuery(oraComm);

            if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
            { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return result;
        }

     

        public int delete(int AwardsID)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "KindAward_PKG.DeleteKindAward";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("INID_AWARD", OracleDbType.Int32);
            oraParam.Value = AwardsID;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            int result = ExecuteNoneQuery(oraComm);

            if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
            { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return result;
        }
        public KindOfAwardsDO getbyAwards(int AwardsID)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "KINDAWARD_PKG.GetByKindAward";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("INID_AWARD", OracleDbType.Int32);
            oraParam.Value = AwardsID;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            DataSet ds = GetDataSet(oraComm);
            DataRow dr = null;
            KindOfAwardsDO vKindOfAwardsDO = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        dr = ds.Tables[0].Rows[0];
                        vKindOfAwardsDO = new KindOfAwardsDO();

                        if (!Convert.IsDBNull(dr["AWARDSID"]))
                        { vKindOfAwardsDO.AwardsID = Convert.ToInt32(dr["AWARDSID"]); }

                        if (!Convert.IsDBNull(dr["AWARDSNAME"]))
                        { vKindOfAwardsDO.AwardsName = Utils.ToUTF8String((byte[])dr["AWARDSNAME"]); }
                        dr = null;
                    }
                }

                ds.Dispose();
                ds = null;
            }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return vKindOfAwardsDO;
        }
        public List<KindOfAwardsDO> SelectALLAwards()
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "KINDAWARD_PKG.GetAllKindAward";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            DataSet ds = GetDataSet(oraComm);
            DataTable dt = null;
            List<KindOfAwardsDO> vKindOfAwardsDOCollection = null;
            DataRow dr = null;
            KindOfAwardsDO vKindOfAwardsDO = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                    vKindOfAwardsDOCollection = new List<KindOfAwardsDO>();

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        dr = dt.Rows[i];
                        vKindOfAwardsDO = new KindOfAwardsDO();

                        if (!Convert.IsDBNull(dr["AWARDSID"]))
                        { vKindOfAwardsDO.AwardsID = Convert.ToInt32(dr["AWARDSID"]); }

                        if (!Convert.IsDBNull(dr["AWARDSNAME"]))
                        { vKindOfAwardsDO.AwardsName = Utils.ToUTF8String((byte[])dr["AWARDSNAME"]) ; }

                        vKindOfAwardsDOCollection.Add(vKindOfAwardsDO);
                        dr = null;
                        vKindOfAwardsDO = null;
                    }

                    dt.Dispose();
                    dt = null;
                }

                ds.Dispose();
                ds = null;
            }
            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return vKindOfAwardsDOCollection;
        }
    }
}
